Generate Documents from Airtable using a Scripting Extension

Getting started with Airtable

In today’s work processes, managers sometimes have to deal with a multitude of tools in use, which reduces productivity and increases the risk of errors when transferring data from one tool to another. So it’s in their interest to find the right combination of tools, for a straightforward and efficient process.

That is why in this article we will create a convenient workflow allowing you to automatically generate documents from your Airtable interface, without having to transfer your data from one application to another. Furthermore, you will be using a Word template, which can be easily edited with the text processing software that you’re most familiar with.

Here are the services needed to create this workflow :

1. Airtable: is a no-code collaboration platform allowing users to interact simply with a database using, among others, a spreadsheet-like interface, web forms for input, and user-friendly data reports and data visualization interfaces for output.

2. DocuGenerate: is a web app and API designed for generating documents from Word templates and JSON data, by replacing merge tags in templates with dynamic data.

This article is mainly intended for people already having a minimal knowledge of Airtable’s basic features. We won’t go into detail about its functionalities, and will only focus on the aspects necessary for setting up our process.

Using Airtable’s page designer

You could use Airtable’s page designer extension, to create your own custom template for invoices, business cards, catalogs, and more — all using the data directly from your records. When you’re done making everything look beautiful, you can print out your records or turn them into PDFs.

There are a few other limitations to consider for our use case:

• The files are generated one by one.
• It can only generate PDF files.
• It cannot store the generated files in your Airtable database.

The page designer layout editor is not that bad, but you will never be as productive as with your usual word processing software. Also, you will have to start from scratch again if you want to create a new layout in another base.

Creating the Word template

Given the limitations of the page designer, we will be using a Word template instead. A template is a document that contains merge tags, which are placeholders intended to be replaced with dynamic data. They allow you to easily create documents that are visually very similar, but differ based on the data set that is used to populate the placeholders.

Our template is a slightly modified version of the Certificate of Completion template from the Template Library. The main changes were the addition of a profile photo to highlight our image resizing feature, and the integration of a skills list, to demonstrate using Airtable’s linked record fields together with DocuGenerate’s list syntax.

This template includes the following tags: {Company_Name}, {First_Name}, {Last_Name}, {Program}, {Photo}, {Start_Date}, {End_Date}, {Certificate_Date} and {Skills} (using the following syntax to create a skills list):

{#Skills}
- {.}
{/Skills}

💡 Going further The enhanced syntax is enabled for this template to allow the image resizing ability. You should avoid having whitespaces in your tag names in order to use it. That is why tag names composed of two words use an underscore between them.

Designing the database

For our example, we started with the template and have then created an appropriate database structure. Of course, a real process will unfold in the other way: as your data will probably already be consistent, you will create a template matching your data and your base structure in order to generate the documents you need to, and not a database structure according to your template.

Here, our base includes three tables :

• Skills table contains the skills acquired in the different programs, ordered by category.

• Programs table contains data about each program : name, start date, end date, and the acquired skills (field linked to the Skills table).

• Attendees table contains data about each attendee of the different programs, such as first name, last name and photo, and also a field link to the pursued program from which several lookup fields are created to access the program’s data directly in the Attendees table. This is needed as the script used to generate documents currently allows one single table selection, so every data has to be present in this table.

Please note that the certificate date needed in the template isn’t stored in the data. Don’t worry about that, it will be populated with today’s date when the document is generated.

If you want to see what our base looks like or start fiddling with it, you can simply explore the DocuGenerate script training data.

Populating the database

Our base is already filled with data allowing to test our workflow, but in an actual process you would need a mechanism to collect this data. To achieve this goal, one of the easiest way is to create forms that users can fill directly by themselves.

A form is basically a web page that you will make accessible to anyone, with several input fields linked to your table’s fields, so filling the form will create a new record in your table. For example, the process for creating a form for the Attendees table is pretty straightforward:

• Create a new form in the Views sidebar.
• Upload a logo and/or a cover image for your form.
• Choose which fields you want the user to fill in.
• For each field specify if it’s required, then add a title and a description.
• Publish your form in order to get a public URL to share.

💡 Going further To learn more about building and sharing forms in Airtable, please refer to their official documentation.

Setting up the scripting extension

Now that our template and our base are ready, we will set up the script used to generate the documents. For that, you will need to add a Scripting Extension in one of your dashboards. Please remember that extensions are only available in paid plans.

Next you’ll have to copy this JavaScript code and paste it in the scripting extension. This code is part of the airtable-docugenerate GitHub repository containing the scripts for generating documents from Airtable using DocuGenerate.

If everything goes well, you should now be able to access the script’s settings page by clicking the gear icon that appears when hovering over the upper right corner of your extension. Do not hesitate to use the full-screen icon next to the settings icon, for better visibility.

Here are the available settings :

• Record table: the table containing the records you will use to replace your template’s tags.

• DocuGenerate API Key: your API Key that you can get from your settings page once you’ve created your account. Simply copy the API Key to the clipboard by clicking on it.

• DocuGenerate ID of the template: the ID of your template. You can copy the ID from your template’s page. Please note that it also appears directly in the URL of your template’s page, the URL being of form https://app.docugenerate.com/templates/:template_id

• DocuGenerate config field (text type field): the field used to store the configuration of relationships between your template and your fields.

• Document storage field (either URL or File type field): the field used to store your document.

• Output format: output format of the generated document. The options are .docx, .pdf, .doc, .odt, .txt or .html

With all these parameters set, we are now ready to automate the process of document creation!

Generating and storing documents

The scripting extension is waiting for the user to input a specific record to process. But in order to make things easier for the end-user, we want to generate the document simply by clicking on a button. For this to work, we’ll need to add a new Button Field, that will be configured to run our script when it’s clicked. This way, the record from which the button was clicked is automatically selected for running the script.

Next we need to do the mapping between the template tags and the record fields. For each available template tag, you will choose how this tag will be replaced. These are the different options available:

• Base’s name: the name of the database.
• Table’s name: the name of the table.
• Today’s date: the date of today. You can choose between fr-FR (DD/MM/YYYY) and en-US (MM/DD/YYYY) date format. Since certificate date is not stored in the database schema, we can use today’s date.
• Time right now : the current time. You can choose between fr-FR (24h based) and en-US (12h based) time format.
• Record field : a specific field of your table (except an image type field, see bellow).
• Image field : an image type field of your table.

To facilitate the mapping, the script automatically compares your record fields with the template tags. If the names match, you will just be asked to confirm this replacement rule. Choosing matching field and tag names will therefore save you precious time.

Once all the tags replacement rules have been set, they are stored in the DocuGenerate config field specified in the Scripting Extension settings. The script then asks if you would like to propagate these rules to an entire view, allowing you not to start this process again from scratch for any other record. The field is read each time you run the script, and if everything matches, you don’t have to redo the mapping.

After validating the tags replacement rules, we can create the document with the database record data when clicking on the Generate button. The generated document will be stored in the Document storage field specified in the Scripting Extension settings.

Going further with Airtable Automations

An interesting prospective for the script development could be to integrate it in an Airtable Automation. Several processes could be envisaged :

• Automatically generate a document when a record matches specific conditions, like updating an attendee status for example.

• Automatically send a document when it’s generated (i.e. when the record matches the specific condition of the document field being not empty).

Unfortunately, for Automation scripts the process is not as simple as for Extension scripts, because you have to manually define the fields used as settings, and it’s not possible to create a simple user interface like for Extension scripts.

Conclusion

The world of automation is endless, and you should definitely keep in mind that such tools should remain processes facilitators, so don’t get lost in their development when it would be quicker not to use them! Recognising this limit can be really frustrating for technology enthusiasts, but it’s a major key to truly effective workflows.

Credits

• All the profile photos are royalty-free and come from Pixabay, the stunning royalty-free images & stock website.

Resources

• GitHub repository containing the Airtable scripts for generating documents using the DocuGenerate API, based on the Airtable Extensions.
• DocuGenerate script training data in Airtable, containing the Skills, Programs and Attendees tables.
• The Certificate of Completion template used in this tutorial.
• An example of a PDF Certificate generated from Airtable data.